<?php
class p_info extends Model{
	public static function getTableName(){
		return 'p_info';
	}

	public function getResultList($infoType, $findRelation, $field, $operation, $value, $start, $length, $sidx	= NULL, $sord = NULL, $unpublish = 0)
    {
		$find	=	$findRelation['mapField'];
        $concat =   $findRelation['columnConcat'];

        $replace=   array(
                        '__dbTable__'       =>  AC::$p_infoType[$infoType],
                        '__infoTitle__'     =>  $concat[$infoType],
                    );

		$subSql	=	'
                SELECT  DISTINCT
                    __dbTable__.id                  AS  id,
                    (
                        SELECT
                            GROUP_CONCAT(inPL.title SEPARATOR "\n\t")
                        FROM
                            `p_episode` AS inEpisode LEFT JOIN `p_programme-language` AS inPL ON (inPL.programme_id = inEpisode.programme_id)
                        WHERE
                            inEpisode.programme_id = outEpisode.programme_id
                            AND inEpisode.id = outEpisode.id
                    )                               AS  title,
                    outEpisode.onair_episode_no     AS  onair_episode_no,
                    concat(__infoTitle__)           AS  infoTitle,
                    __dbTable__.status              AS  record_status,
                    __dbTable__.modified_at         AS  modified_at,
                    __dbTable__.modified_by         AS  modified_by
                FROM
                    __dbTable__, `p_episode` AS outEpisode
                WHERE
                    __dbTable__.episode_id = outEpisode.id
                    ';
        if (!empty($field['onair_episode_no']))
        {
            $subSql .=  'AND outEpisode.onair_episode_no = "__episodeNo__"
                    ';
            $replace['__episodeNo__']   =   $field['onair_episode_no'];
        }
        if (!empty($field['programme_id']))
        {
            $subSql .=  'AND outEpisode.programme_id = "__programmeId__"
                ';
            $replace['__programmeId__'] =   $field['programme_id'];
        }
        $subSql =   Model::replaceString($subSql, $replace);
		#echo $subSql;

		$sql=	'
			SELECT
				id,
				title,
				onair_episode_no,
                infoTitle,
                record_status,
				modified_at,
				modified_by
			FROM
			(
				'. $subSql. '
			)	AS	findApi
			';

		# Order By ...
		$orderBy=	$orderTmp   =   NULL;
		if (!empty($sidx)	&&	!empty($find[$sidx]))
		{
			$orderTmp	=	$find[$sidx];
			if ('desc'	==	$sord)
			{
				$orderTmp	.=	' DESC ';
			}
			elseif ('asc'	==	$sord)
			{
				$orderTmp	.=	' ASC ';
            }
            $orderBy[]  =   $orderTmp;
		}

        $orderBy[]  =   '`findApi`.`modified_at` DESC';

        $orderBy    =   ' ORDER BY '. implode(', ', $orderBy);
		$dataSql	=	$sql. $orderBy. ' LIMIT '. $start. ', '. $length;
		$totalSql	=	'SELECT count(*) AS num FROM ('. $sql. ')	AS	tmp';

		$db_con = ($unpublish == 1) ? CommonUtils::getDbConn(0,'preview') : Model::$dbo['r'];
		$result	=	$db_con->findBySql(__class__,$dataSql);
		$retAll	=	$db_con->findBySql(__class__,$totalSql);

		$list	=	array();
		$list['page']	=	ceil($start / $length) + 1;
		$list['total']	=	ceil($retAll[0]->num / $length);
		$list['records']=	$retAll[0]->num;

		foreach	($result	as	$gridData)
		{
			$tmp			=	array();
			$tmp['id']		=	$gridData->id;
			$tmp['cell']	=	array();

			$gridData->title    = explode("\n\t",$gridData->title);
			$gridData->title[0] = htmlentities($gridData->title[0],ENT_QUOTES,'UTF-8');
			$gridData->title[1] = isset($gridData->title[1]) ? htmlentities($gridData->title[1],ENT_QUOTES,'UTF-8') : '';
			$gridData->title    = implode('<br />',$gridData->title);

			$gridData->infoTitle    = explode("\n\t",$gridData->infoTitle);
			$gridData->infoTitle[0] = htmlentities($gridData->infoTitle[0],ENT_QUOTES,'UTF-8');
			$gridData->infoTitle[1] = isset($gridData->infoTitle[1]) ? htmlentities($gridData->infoTitle[1],ENT_QUOTES,'UTF-8') : '';
			$gridData->infoTitle    = implode('<br />',$gridData->infoTitle);


			$gridData->modified_at= str_replace(' ','<br />',$gridData->modified_at);

            $originalStatus =   $gridData->record_status;
            if (!empty($unpublish))
            {
               // $publish    =   Model::object2array(publish_log::getPublishLog(AC::$infoType[$infoType], $gridData->id));
                $_publish = publish_log::getPublishLog(AC::$p_infoType[$infoType], $gridData->id);
                $publish    = count((array)$_publish) <= 0 ? array() :  Model::object2array($_publish);
                $publish = isset($publish['premium']) ? $publish['premium'] : array();
                $gridData->record_status    =   NULL;
                $gridData->modified_by      =   NULL;
                $gridData->modified_at      =   NULL;
                if (isset($publish['action'])   &&  isset(AC::$publicLogAction[$publish['action']]))
                {
                    $gridData->record_status    =   AC::$publicLogAction[$publish['action']];
                    isset($publish['published_by']) &&  $gridData->modified_by  =   $publish['published_by'];
                    isset($publish['published_at']) &&  $gridData->modified_at  =   $publish['published_at'];
                }
            }
            else
            {
                isset(AC::$op_status[$gridData->record_status]) &&  $gridData->record_status    =   AC::$op_status[$gridData->record_status];
            }
			foreach ($find	as	$name	=>	$column)
			{
                $tmp['cell'][]	=	$gridData->$name;
			}

            $delEdit    =   $delView    =   NULL;
            if (AC::STATUS_DELETED !=   $originalStatus &&  empty($unpublish))
            {
                $delEdit    =   '<a title="Edit" href="get_info.html?act=edit&type='. $infoType. '&id='. $gridData->id. '" class="btn edit ui-state-default ui-corner-all"><span class="ui-icon ui-icon-pencil"/>Edit</a>';
            }
            if (empty($unpublish)   ||  (isset($publish['action'])  &&  AC::LOG_OP_PREMIUM_PUBLISH  ==  $publish['action']))
            {
                $delView    =   '<a title="View" href="get_p_info.html?act=view&type='. $infoType. '&id='. $gridData->id .'&unpublish='. $unpublish. '" class="btn view ui-state-default ui-corner-all"><span class="ui-icon ui-icon-image"/>View</a>';
            }
            $tmp['cell'][]  =   $delEdit. $delView;
			$list['rows'][]	=	$tmp;
            unset($publish);
		}

		return	$list;
	}

    /**
     * get proofread SQL
     * @param   $id Int
     * @return  Array
     */
    public function getProofread($id)
    {
        if (empty($id)) return  array();
		
        $sqlArr = array(
			'info_address'              => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_award'                => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_biography'            => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_general_information'	=> array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_pageant' 				=> array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_product'              => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_q_a'                  => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_recipe'               => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
			'info_song'                 => array('episode_id = "'. $id. '"', 'status <> "'. AC::STATUS_DELETED. '"'),
		);

        $sqlArray   =   array();
        foreach ($sqlArr    as  $tableName  =>  $whereArray)
        {
            $sqlArray[$tableName]   =   Model::assembleGetProofreadSql($tableName, $whereArray);
        }
        return  $sqlArray;
    }
}
